﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Odbc;

namespace BSE.Windows.Test
{
    class AnesthesiaDAL
    {
        private OdbcConnection odbcConn;

        private static OdbcCommand odbcCmd = new OdbcCommand();

        private OdbcDataAdapter odbcDa;
        private OdbcDataReader odbcDr;
        private DataSet Ds;
        private object obj;
        private OdbcTransaction myTrans;

        public readonly string connString = "Driver={Microsoft Access Driver (*.mdb)};"
            + "Dbq=.\\Anestheisa.mdb;Uid=Admin;Pwd=;";


        //打开数据库 开始Transaction
        private OdbcConnection GetConnection1()
        {
            odbcConn = new OdbcConnection();

            odbcConn.ConnectionString = this.connString;
            //if (this.odbcConn.State == ConnectionState.Closed)
            //{
            odbcConn.Open();
            //}
            odbcCmd.Connection = odbcConn;
            myTrans = odbcCmd.Connection.BeginTransaction();  //使用New新生成一个事务
            odbcCmd.Transaction = myTrans;

            return odbcConn;
        }

        //打开数据库,获取连接数据库字符串
        private OdbcConnection GetConnection()
        {
            odbcConn = new OdbcConnection();

            odbcConn.ConnectionString = this.connString;
            if (this.odbcConn.State != ConnectionState.Open)
                odbcConn.Open();

            return odbcConn;
        }



        //关闭数据库
        private void CloseConnection()
        {
            if (this.odbcConn.State == ConnectionState.Open)
                odbcConn.Close();
        }

        public DataTable GetDataTable(string sql)
        {
            Ds = new DataSet();
            odbcDa = new OdbcDataAdapter();
            odbcDa.SelectCommand = new OdbcCommand();

            odbcDa.SelectCommand.CommandText = sql;
            odbcDa.SelectCommand.Connection = this.GetConnection();
            odbcDa.SelectCommand.CommandType = CommandType.Text;

            odbcDa.Fill(Ds);

            this.CloseConnection();

            return Ds.Tables[0];
        }

        public DataSet GetDataSet(string sql)
        {
            Ds = new DataSet();
            odbcDa = new OdbcDataAdapter();
            odbcDa.SelectCommand = new OdbcCommand();

            odbcDa.SelectCommand.CommandText = sql;
            odbcDa.SelectCommand.Connection = this.GetConnection();
            odbcDa.SelectCommand.CommandType = CommandType.Text;

            odbcDa.Fill(Ds);

            this.CloseConnection();

            return Ds;
        }

        public DataTable GetDataTable(string sql, CommandType cmdType)
        {
            Ds = new DataSet();
            odbcDa = new OdbcDataAdapter();
            odbcDa.SelectCommand = new OdbcCommand();

            odbcDa.SelectCommand.CommandText = sql;
            odbcDa.SelectCommand.Connection = this.GetConnection();
            odbcDa.SelectCommand.CommandType = cmdType;

            odbcDa.Fill(Ds);

            this.CloseConnection();

            return Ds.Tables[0];
        }

        public OdbcDataReader GetDataReader(string sql)
        {
            //odbcCmd = new OdbcCommand();

            odbcCmd.CommandText = sql;
            odbcCmd.Connection = this.GetConnection();
            odbcCmd.CommandType = CommandType.Text;

            odbcDr = odbcCmd.ExecuteReader(CommandBehavior.CloseConnection);

            return odbcDr;
        }

        public OdbcDataReader GetDataReader(string sql, CommandType cmdType)
        {
            //odbcCmd = new OdbcCommand();

            odbcCmd.CommandText = sql;
            odbcCmd.Connection = this.GetConnection();
            odbcCmd.CommandType = cmdType;

            odbcDr = odbcCmd.ExecuteReader(CommandBehavior.CloseConnection);

            return odbcDr;
        }

        public object GetDataScalar(string sql)
        {
            //odbcCmd = new OdbcCommand();

            odbcCmd.CommandText = sql;
            odbcCmd.Connection = this.GetConnection();
            odbcCmd.CommandType = CommandType.Text;

            obj = odbcCmd.ExecuteScalar();

            this.CloseConnection();

            return obj;
        }

        public object GetDataScalar(string sql, CommandType cmdType)
        {
            //odbcCmd = new OdbcCommand();

            odbcCmd.CommandText = sql;
            odbcCmd.Connection = this.GetConnection();
            odbcCmd.CommandType = cmdType;

            obj = odbcCmd.ExecuteScalar();

            this.CloseConnection();

            return obj;
        }


        // 执行数据库操作
        public int ExecuteNonQuery(string sql)
        {
            //odbcCmd = new OdbcCommand();

            int rowCount = 0;
            odbcCmd.CommandText = sql;
            odbcCmd.Connection = this.GetConnection();//调用函数打开数据库
            myTrans = odbcCmd.Connection.BeginTransaction(); //使用New新生成一个事务
            odbcCmd.Transaction = myTrans;
            try
            {
                odbcCmd.CommandType = CommandType.Text;
                rowCount = odbcCmd.ExecuteNonQuery();
                myTrans.Commit();  //提交事务
            }
            catch (Exception e)
            {
                myTrans.Rollback(); //回滚事务
                throw new Exception("数据异常,已回滚");
            }
            finally
            {
                this.CloseConnection();
            }

            return rowCount;
        }


        // 执行数据库操作  BeginTransaction 为True 打开事务；EndTransaction 为True 提交事务
        public int ExecuteNonQuery(string sql, bool BeginTransaction, bool EndTransaction)
        {
            int rowCount = 0;
            odbcCmd.CommandText = sql;
            if (BeginTransaction)
            {
                odbcCmd.Connection = this.GetConnection();//调用函数打开数据库
                myTrans = odbcCmd.Connection.BeginTransaction(); //使用New新生成一个事务
                odbcCmd.Transaction = myTrans;
            }
            try
            {
                odbcCmd.CommandType = CommandType.Text;
                rowCount = odbcCmd.ExecuteNonQuery();
                if (EndTransaction)
                {
                    myTrans.Commit();  //提交事务
                }
            }
            catch (Exception e)
            {
                myTrans.Rollback(); //回滚事务
                throw new Exception("数据异常,已回滚");
            }
            finally
            {
                if (EndTransaction)
                {
                    this.CloseConnection();
                }
            }

            return rowCount;
        }

        //返回首行首列 不关闭连接
        public object GetDataScalarTransaction(string sql)
        {
            odbcCmd.CommandText = sql;
            odbcCmd.CommandType = CommandType.Text;

            obj = odbcCmd.ExecuteScalar();

            return obj;
        }

        //返回DataTable 不关闭连接
        public DataTable GetDataTableTransaction(string sql)
        {
            Ds = new DataSet();
            odbcDa = new OdbcDataAdapter();
            odbcDa.SelectCommand = new OdbcCommand();

            odbcDa.SelectCommand.CommandText = sql;
            odbcDa.SelectCommand.Connection = this.GetConnection();
            odbcDa.SelectCommand.CommandType = CommandType.Text;

            odbcDa.Fill(Ds);

            return Ds.Tables[0];
        }


        public int ExecuteNonQuery(string sql, params OdbcParameter[] cmdParms)
        {
            //odbcCmd = new OdbcCommand();

            int rowCount = 0;
            odbcCmd.CommandText = sql;
            odbcCmd.Connection = this.GetConnection();
            myTrans = odbcCmd.Connection.BeginTransaction(); //使用New新生成一个事务
            odbcCmd.Transaction = myTrans;
            try
            {
                odbcCmd.CommandType = CommandType.Text;

                if (cmdParms != null)
                {
                    foreach (OdbcParameter parm in cmdParms)
                        odbcCmd.Parameters.Add(parm);
                }

                rowCount = odbcCmd.ExecuteNonQuery();
                myTrans.Commit();  //提交事务
            }

            catch (Exception e)
            {
                myTrans.Rollback(); //回滚事务
                throw new Exception("数据异常,已回滚");
            }
            finally
            {
                this.CloseConnection();
            }
            return rowCount;
        }


        public DataTable GetDataTable(string sql, CommandType cmdType, params OdbcParameter[] cmdParms)
        {
            Ds = new DataSet();
            odbcDa = new OdbcDataAdapter();
            odbcDa.SelectCommand = new OdbcCommand();

            odbcDa.SelectCommand.CommandText = sql;
            odbcDa.SelectCommand.Connection = this.GetConnection();
            odbcDa.SelectCommand.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (OdbcParameter parm in cmdParms)
                    odbcDa.SelectCommand.Parameters.Add(parm);
            }

            odbcDa.Fill(Ds);

            this.CloseConnection();

            return Ds.Tables[0];
        }

    }
}
